SQL: Logical Operators

BETWEEN

LIKE

Ah, BETWEEN—the SQL operator that’s like a bouncer at a club, deciding who gets in and who’s left out. It’s the ultimate gatekeeper, saying, 'You can come in if you’re between these two numbers, but if you’re outside this exclusive range? Sorry, buddy, you’re not on the list!' Whether you’re sifting through prices, dates, or names, BETWEEN is here to help you draw arbitrary lines in the sand, because who doesn’t love a good range?

The BETWEEN operator in SQL is used to filter the result set within a certain range. It is commonly used in the WHERE clause to specify a range of values for a column. The BETWEEN operator is inclusive, meaning it includes the boundary values specified in the range.

SQL Syntax: Using BETWEEN

Example:

Employee Table

ID | F_Name  | L_Name  | Salary  
---| --------|---------|--------
 1 | Alice   | Johnson | 70,000 
 2 | Bob     | Smith   | 35,000 
 3 | Raymond | Barone  | 65,000 
 4 | Michael | Scott   | 90,000 
 5 | John    | Doe     | 52,000 
 6 | Bill    | Jacobs  | 38,000 
 7 | Joseph  | Roberts | 85,000

In the following examples pay attention to the placement of '%'. In SQL, string values must be enclosed in quotes when searching.

Numeric Ranges

Prompt

Find all employees with a salary between $40,000 and $80,000:

Query

SELECT * 
FROM Employees 
WHERE Salary BETWEEN 40000 AND 80000;

Result

ID | F_Name  | L_Name  | Salary  
---| --------|---------|--------
 1 | Alice   | Johnson | 70,000 
 3 | Raymond | Barone  | 65,000 
 5 | John    | Doe     | 52,000

This returned Alice, Raymond, and Bill as their salaries are within the specified range.

Endpoints Included

Prompt

Find all employees with IDs between 2 and 5

Query

SELECT * 
FROM Employees 
WHERE ID BETWEEN 2 AND 5;

Result

ID | F_Name  | L_Name  | Salary  
---| --------|---------|--------
 2 | Bob     | Smith   | 35,000 
 3 | Raymond | Barone  | 65,000 
 4 | Michael | Scott   | 90,000 
 5 | John    | Doe     | 52,000

This returned Bob, Raymond, Michael, and John. BETWEEN is inclusive of it's end points so ID's 2 and 5 are included.

String Ranges

Prompt

Find employees with last names between 'Barone' and 'Roberts'

Query

SELECT * 
FROM Employees 
WHERE L_Name BETWEEN 'Barone'
		AND 'Roberts';

Result

ID | F_Name  | L_Name  | Salary  
---| --------|---------|--------
 1 | Alice   | Johnson | 70,000 
 3 | Raymond | Barone  | 65,000 
 5 | John    | Doe     | 52,000 
 6 | Bill    | Jacobs  | 38,000 
 7 | Joseph  | Roberts | 85,000

This returned Alice, Raymond, John, Bill , and Joseph. It excluded Michael Scott and Bob Smith as their last names begin with 'S'.

LIKE Using _

Let's change the table slightly to perform date ranges:

F_Name  | L_Name  | DOB  
--------|---------|-----------
Alice   | Johnson | 1990-02-20 
Raymond | Barone  | 1987-08-12 
John    | Doe     | 2000-06-08
Bill    | Jacobs  | 1995-10-15 
Joseph  | Roberts | 1981-11-23

Prompt

Find all employees born between January 1, 1980, and December 31, 1990

Query

SELECT * 
FROM Employees 
WHERE DOB BETWEEN '1980-01-01' 
              AND '1990-12-31';

Result

F_Name | L_Name | DOB --------|---------|----------- Alice | Johnson | 1990-02-20 Raymond | Barone | 1987-08-12 Joseph | Roberts | 1981-11-23

This returned Alice, Raymond, and Joseph

Wrapping Up

In summary, the BETWEEN operator in SQL is a valuable tool for filtering records within a specified range, whether for numbers, dates, or text. Its simple syntax enhances query readability and efficiency, making it essential for effective data retrieval and analysis.